package bulk_param.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import bulk_param.modele.OuverturePointModel;


public class SelectBoxOuverturePointDao {
	
	public OuverturePointModel ouverturePointModel;
	private String userid = "arbor";
    private String password = "arbor123";
    private String pilote = "oracle.jdbc.driver.OracleDriver";
    private String url = "jdbc:oracle:thin:@10.1.3.136:1522:cc_cust"; 

	private static Connection con;

     // constructor 
	public SelectBoxOuverturePointDao(){
		this.ouverturePointModel= new OuverturePointModel();
	}

	public Connection getConnection(){
				
		try {
			Class.forName(pilote);	

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
			con = DriverManager.getConnection(url, userid, password);
			System.out.println ("Ouverture Connection");     
		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		
		return con;
	}
	
	static  public  void  closeConnection(){         
		System.out.println ("Colse DBConnection.");          
		        
				
		try {
			con.close();             
			System.out.println ("Fermeture Connection");        
			}         
		catch (Exception ex) {             
			System.out.println ("ERROR :"+ex.getMessage());             
			ex.printStackTrace();        
			}    
		} 
	
			
	public ArrayList<String> listOperateurNationalFixe09(){
	
		ArrayList <String> listOperateur = new ArrayList<String>();
		try{
			String sql="SELECT MAX ( access_region ) access_region,"
						+"UPPER ( display_value ) operateurs "
             			+"FROM ACCESS_REGION_VALUES "
  						+"WHERE TO_NUMBER ( access_region ) BETWEEN 3000 AND 3200 "
						+"AND access_region NOT IN ( 3082, 3168, 3080, 3081, 3079,"
                                                +"3058, 3062, 3063, 3064, 3060,"
                                                +"3057, 3105, 3084, 3083 ) "
                        +"AND language_code = 2 "
     					+"GROUP BY display_value "
						+"ORDER BY display_value ";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			while(rs.next()){  
				listOperateur.add(rs.getString("operateurs"));	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return listOperateur;
	}
	
	public ArrayList<String> listOperateurNationalMobile(){
		
		ArrayList <String> listOperateurMob = new ArrayList<String>();
		
		try{
			String sql="SELECT access_region,display_value "
					+"FROM ACCESS_REGION_VALUES "
					+"WHERE access_region IN ( 160, 161, 162, 168, 173, 174, 175, 176 ) "
					+"AND language_code = 2"
					+"ORDER BY TO_NUMBER ( access_region )";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){  
				listOperateurMob.add(rs.getString("display_value"));	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return listOperateurMob;
	}
	
	public ArrayList<String> listPointRegionNational(){
		
		ArrayList <String> listPointRegionNat = new ArrayList<String>();
		
		try{
			String sql="SELECT point_region, display_value "
							+ "FROM POINT_REGION_VALUES "
							+ "WHERE point_region IN ( 100, 102, 104 ) "
							+ "AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){  
				listPointRegionNat.add(rs.getString("display_value"));	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return listPointRegionNat;
	}
	
	public ArrayList<String> listZNENational(){
		
		ArrayList <String> listZNENat = new ArrayList<String>();
		
		try{
			String sql="SELECT point_class, display_value "
					+"FROM POINT_CLASS_VALUES "
					+"WHERE point_class > 10000 AND language_code = 2 " 
					+"ORDER BY display_value";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){  
				listZNENat.add(rs.getString("display_value"));	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return listZNENat;
	}
	
	
	//INTERNATIONAL
		
	//ComboBox Pays Mobile International
	public ArrayList<String> listPaysMobInternational(){
		
		ArrayList <String> listPaysMobInter = new ArrayList<String>();
		
		try{
			String sql="SELECT MAX ( access_region ), display_value "
						+"FROM ACCESS_REGION_VALUES "
	   					+"WHERE UPPER ( display_value ) LIKE '%MOBILE%' "
								+"AND TO_NUMBER ( access_region ) BETWEEN 1000 AND 2999 "
	     						+"AND TO_NUMBER ( access_region ) NOT BETWEEN 2045 AND 2123 "
	     						+"AND TO_NUMBER ( access_region ) NOT BETWEEN 1569 AND 1584 "
	     						+"AND TO_NUMBER ( access_region ) NOT BETWEEN 1904 AND 1905 "
	     						+"OR TO_NUMBER ( access_region ) =47 "
	     						+"OR TO_NUMBER ( access_region ) =55 "
	     						+"OR TO_NUMBER ( access_region ) =40 "
	     						+"OR TO_NUMBER ( access_region ) =44 "
	     						+"OR TO_NUMBER ( access_region ) =41 "
	    				+"AND language_code = 2 "
	     				+"GROUP BY display_value "
						+"ORDER BY display_value";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){  
				listPaysMobInter.add(rs.getString("display_value"));	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return listPaysMobInter;
	}
	
	//ComboBox Pays Fixe International
	public ArrayList<String> listPaysFixeInternational(){
		
		ArrayList <String> listPaysFixeInter = new ArrayList<String>();
		
		try{
			String sql="SELECT MAX ( access_region ), display_value "
						+"FROM ACCESS_REGION_VALUES "
	   					+"WHERE UPPER ( display_value ) NOT LIKE '%MOBILE%' "
								+"AND TO_NUMBER ( access_region ) BETWEEN 1000 AND 2999 "
	     						+"AND TO_NUMBER ( access_region ) NOT BETWEEN 2045 AND 2123 "
	     						+"AND TO_NUMBER ( access_region ) NOT BETWEEN 1569 AND 1584 "
	     						+"AND TO_NUMBER ( access_region ) NOT BETWEEN 1904 AND 1905 "
	    				+"AND language_code = 2 "
	     				+"GROUP BY display_value "
						+"ORDER BY display_value";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){  
				listPaysFixeInter.add(rs.getString("display_value"));	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return listPaysFixeInter;
	}
	
	
	//ComboBox Point Region International
	public ArrayList<String> listPointRegionInternational(){
		
		ArrayList <String> listPointRegionInter = new ArrayList<String>();
		
		try{
			String sql="SELECT point_region, display_value "
							+ "FROM POINT_REGION_VALUES "
							+ "WHERE point_region IN ( 1,2,3,4,5,6,20) "
							+ "AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){  
				listPointRegionInter.add(rs.getString("display_value"));	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return listPointRegionInter;
	}

	
}
	
